Skip to main content

Levels

You can obtain all the details you need for the levels in a specified catalog.

Request​

A query for levels must include the following:

  • The SELECT statement must start with one or more level parameters (comma separated).
  • After the parameters you must provide the following data source: FROM $system.MDSCHEMA_LEVELS
  • The name of the published catalog (AtScale project) for which you need level information must be specified in the Properties > PropertyList > Catalog element. For details on obtaining a catalog name, see Catalogs.

You can request the following level parameters:

  • CATALOG_NAME
  • CUBE_GUID
  • CUBE_NAME
  • CUSTOM_ROLLUP_SETTINGS
  • DATASET_NAME
  • DESCRIPTION
  • DIMENSION_UNIQUE_NAME
  • HIERARCHY_UNIQUE_NAME
  • IS_PRIMARY
  • LEVEL_ATTRIBUTE_HIERARCHY_NAME
  • LEVEL_CAPTION
  • LEVEL_CARDINALITY
  • LEVEL_DBTYPE
  • LEVEL_DBTYPE_NAME_COLUMN
  • LEVEL_DBTYPE_SORT_COLUMN
  • LEVEL_GUID
  • LEVEL_IS_VISIBLE
  • LEVEL_KEY_CARDINALITY
  • LEVEL_KEY_SQL_COLUMN_NAME
  • LEVEL_MASTER_UNIQUE_NAME
  • LEVEL_NAME
  • LEVEL_NAME_SQL_COLUMN_NAME
  • LEVEL_NUMBER
  • LEVEL_ORDERING_PROPERTY
  • LEVEL_ORIGIN
  • LEVEL_SECONDARY_ATTRIBUTE_FOLDER
  • LEVEL_SORT_SQL_COLUMN_NAME
  • LEVEL_TYPE
  • LEVEL_UNIQUE_NAME
  • LEVEL_UNIQUE_NAME_SQL_COLUMN_NAME
  • LEVEL_UNIQUE_SETTINGS
  • PARENT_LEVEL_ID
  • SCHEMA_NAME

For details about the parameter types, see the xsd:schema element in the sample response provided below.

Response​

The response contains a <row> element for each level in the specified catalog. Each of these elements contains the values of the requested parameters.

Sample request​

The example here shows how to make a request with the curl tool:

  • The requested data are the level name (LEVEL_NAME), the dimension name (DIMENSION_UNIQUE_NAME), and the hierarchy name (HIERARCHY_UNIQUE_NAME).
  • The request is for the levels in the Sales Insights catalog.
  • The token is obtained in advance.
  • The query is for the default organization.
  • The address of the AtScale system is: http://example.com:10502/xmla/default

The XML part of the request is as follows:

<?xml version="1.0"?>
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
  <Body>
    <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
      <Command>
        <Statement>SELECT LEVEL_NAME, DIMENSION_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME FROM $system.MDSCHEMA_LEVELS</Statement>
      </Command>
      <Properties>
        <PropertyList>
          <Catalog>Sales Insights</Catalog>
        </PropertyList>
      </Properties>
    </Execute>
  </Body>
</Envelope>

Here is the full form of the curl request:

curl -X POST \
-H "Authorization:Bearer $token" -H 'Content-Type: application/xml' \
-d '<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">\
<Body><Execute xmlns="urn:schemas-microsoft-com:xml-analysis">\
  <Command>\
    <Statement>SELECT LEVEL_NAME, DIMENSION_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME FROM $system.MDSCHEMA_LEVELS</Statement>\
  </Command>\
  <Properties><PropertyList>\
    <Catalog>Sales Insights</Catalog>
  </PropertyList></Properties>\
</Execute></Body></Envelope>' \
http://example.com:10502/xmla/default

Sample response​

The response for the sample request above contains <row> elements for each level found. As requested, each of these elements contains only the level, dimension, and hierarchy name:

<row>
  <LEVEL_NAME>Customer Name</LEVEL_NAME>
  <DIMENSION_UNIQUE_NAME>[Customer Dimension]</DIMENSION_UNIQUE_NAME>
  <HIERARCHY_UNIQUE_NAME>[Customer Dimension].[Customer Hierarchy]</HIERARCHY_UNIQUE_NAME>
</row>
<row>
  <LEVEL_NAME>(All)</LEVEL_NAME>
  <DIMENSION_UNIQUE_NAME>[Customer Dimension]</DIMENSION_UNIQUE_NAME>
  <HIERARCHY_UNIQUE_NAME>[Customer Dimension].[Occupation]</HIERARCHY_UNIQUE_NAME>
</row>
<row>
  <LEVEL_NAME>Occupation</LEVEL_NAME>
  <DIMENSION_UNIQUE_NAME>[Customer Dimension]</DIMENSION_UNIQUE_NAME>
  <HIERARCHY_UNIQUE_NAME>[Customer Dimension].[Occupation]</HIERARCHY_UNIQUE_NAME>
</row>

Here is the full response:

<?xml version="1.0"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Header>
    <as:queryId xmlns:as="http://xsd.atscale.com/soap_v1" soap:mustUnderstand="0">b80ade26-df1d-430a-a733-fb5a8fe3297c</as:queryId>
  </soap:Header>
  <soap:Body>
    <ExecuteResponse xmlns="urn:schemas-microsoft-com:xml-analysis">
      <return>
        <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:msxmla="http://schemas.microsoft.com/analysisservices/2003/xmla" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
          <xsd:schema xmlns:sql="urn:schemas-microsoft-com:xml-sql" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" elementFormDefault="qualified">
            <xsd:element name="root">
              <xsd:complexType>
                <xsd:sequence minOccurs="0" maxOccurs="unbounded">
                  <xsd:element name="row" type="row"/>
                </xsd:sequence>
              </xsd:complexType>
            </xsd:element>
            <xsd:simpleType name="uuid">
              <xsd:restriction base="xsd:string">
                <xsd:pattern value="[0-9a-zA-Z]{8}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{12}"/>
              </xsd:restriction>
            </xsd:simpleType>
            <xsd:complexType name="xmlDocument">
              <xsd:sequence>
                <xsd:any/>
              </xsd:sequence>
            </xsd:complexType>
            <xsd:complexType name="row">
              <xsd:sequence>
                <xsd:element sql:field="LEVEL_NAME" name="LEVEL_NAME" type="xsd:string" minOccurs="0"/>
                <xsd:element sql:field="DIMENSION_UNIQUE_NAME" name="DIMENSION_UNIQUE_NAME" type="xsd:string" minOccurs="0"/>
                <xsd:element sql:field="HIERARCHY_UNIQUE_NAME" name="HIERARCHY_UNIQUE_NAME" type="xsd:string" minOccurs="0"/>
              </xsd:sequence>
            </xsd:complexType>
          </xsd:schema>
          <row>
            <LEVEL_NAME>Customer Name</LEVEL_NAME>
            <DIMENSION_UNIQUE_NAME>[Customer Dimension]</DIMENSION_UNIQUE_NAME>
            <HIERARCHY_UNIQUE_NAME>[Customer Dimension].[Customer Hierarchy]</HIERARCHY_UNIQUE_NAME>
          </row>
          <row>
            <LEVEL_NAME>(All)</LEVEL_NAME>
            <DIMENSION_UNIQUE_NAME>[Customer Dimension]</DIMENSION_UNIQUE_NAME>
            <HIERARCHY_UNIQUE_NAME>[Customer Dimension].[Occupation]</HIERARCHY_UNIQUE_NAME>
          </row>
          <row>
            <LEVEL_NAME>Occupation</LEVEL_NAME>
            <DIMENSION_UNIQUE_NAME>[Customer Dimension]</DIMENSION_UNIQUE_NAME>
            <HIERARCHY_UNIQUE_NAME>[Customer Dimension].[Occupation]</HIERARCHY_UNIQUE_NAME>
          </row>
        </root>
      </return>
    </ExecuteResponse>
  </soap:Body>
</soap:Envelope>

More information​